/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ManageDAL;

import ManageBLL.BLLProduct;
import java.sql.*;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.*;
import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;


public class DALProduct {
    
    AccessDB access = new AccessDB();
    //method getAllProduct from database.
    public List<BLLProduct> getAllProduct()
    {
        try
        {
            List<BLLProduct> listPro = new ArrayList<>();
            String queryString = "select * from product";
            ResultSet rs = access.executeQuery(queryString);
            while(rs.next())
            {
                listPro.add(new BLLProduct(rs));
            }
            return listPro;
        }
        catch(Exception e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage());
            return null;
        }
    }
    
    //method search product with query
    public List<BLLProduct> getProductbyQuery(String productName, String manu)
    {
        try{
            List<BLLProduct> list = new ArrayList<>();
            String queryString = "select * from product, manufacture where product.manufacture_id = manufacture.manufacture_id";
            if(!"".equals(productName))
                queryString += " and product_name like '%" + productName + "%'";
            if(!"Tất cả các hãng...".equals(manu))
                queryString += " and manufacture_name = '" + manu + "'";
            ResultSet rs = access.executeQuery(queryString);
            while(rs.next())
            {
                list.add(new BLLProduct(rs));
            }
            return list;
        }
        catch(Exception e){
            System.err.print(e.getMessage());
            return null;
        }
    }
    
    //method insert new row to table product
    public boolean insertRowProduct(BLLProduct bllPro)
    {
        try{
            DateFormat dateF = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = dateF.format(bllPro.getProductDateUpdate());
            String insertString = String.format("insert into product(product_name, product_quantity, product_price, product_image, "
                    + "product_dateupdate, product_description, product_warranty, product_promotion, manufacture_id) "
                    + "values(N'%s', %d, %d, N'%s', '%s', N'%s', N'%s', N'%s', %d)", bllPro.getProductName(), bllPro.getProductQuantity(),
                    bllPro.getProductPrice(), bllPro.getProductImage(), datePro, bllPro.getProductDescription(),
                    bllPro.getProductWarranty(), bllPro.getProductPromotion(), bllPro.getBllManu().getManufatureID());
//            System.out.print(insertString);
            if(access.exeUpdate(insertString))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.print(ex.getMessage());
            return false;
        }
    }
    
    //method getProduct with ProductID
    public BLLProduct getProductbyID(int ID)
    {
        try{
            BLLProduct bllPro = new BLLProduct();
            String queryString = "select * from product where product_id = " + ID;
            ResultSet rs = access.executeQuery(queryString);
            while(rs.next()){
                bllPro = new BLLProduct(rs);
            }
            return bllPro;
        }
        catch(Exception ex)
        {
            System.err.println(ex.getMessage());
            return null;
        }
    }
    
    //method update product
    public boolean updateProduct(BLLProduct bllPro)
    {
        try
        {
            DateFormat df = new SimpleDateFormat("yyyy-MM-dd");
            String datePro = df.format(bllPro.getProductDateUpdate());
            String queryUpdate;
            queryUpdate = String.format("update product set product_name = N'%s',"
                    + " product_price = %d,"
                    + " product_warranty = N'%s',"
                    + " product_description = N'%s',"
                    + " product_promotion = N'%s',"
                    + " product_image = '%s',"
                    + " product_dateupdate = '%s',"
                    + " manufacture_id = %d where product_id = %d", bllPro.getProductName(), bllPro.getProductPrice(),
                    bllPro.getProductWarranty(), bllPro.getProductDescription(), bllPro.getProductPromotion(),
                    bllPro.getProductImage(), datePro, bllPro.getBllManu().getManufatureID(), bllPro.getProductID());
            System.out.printf(queryUpdate);
            if(access.exeUpdate(queryUpdate))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.println(ex.getMessage());
            return false;
        }
    }
    
    //method delete product
    public boolean deleteProduct(int proID)
    {
        try{
            String queryDel = "delete from product where product_id = " + proID;
            if(access.exeUpdate(queryDel))
                return true;
            return false;
        }
        catch(Exception ex)
        {
            System.err.printf(ex.getMessage());
            return false;
        }
    }
    ////////////////////////////////////////////////////////////
    public DefaultTableModel getDefaultTbModel()
    {
        try{
            String queryString = "select * from product";
            ResultSet rs = access.executeQuery(queryString);
            ResultSetMetaData rsMetaData = rs.getMetaData();
            DefaultTableModel dtm = new DefaultTableModel();
            
            int col_number = rsMetaData.getColumnCount();
            //Vector col_names = new Vector();
            Vector data_rows = new Vector();
            String[] col_names = {"Mã SP", "Tên SP", "SL", "Giá", "Hình", "Ngày cập nhật", "Mô tả", "Bảo hành", "Khuyến mãi"};
//            for (int i = 1; i < col_number; i++) {
//                col_names.addElement(rsMetaData.getColumnName(i));
//            }
            dtm.setColumnIdentifiers(col_names);
            
            while(rs.next())
            {
                data_rows = new Vector();
                for (int i = 1; i < col_number; i++) {
                    data_rows.addElement(rs.getString(i));
                }
                dtm.addRow(data_rows);
            }
            return dtm;
        }
        catch(Exception e)
        {
            JOptionPane.showMessageDialog(null, e.getMessage());
            return null;
        }
    }
}
